package com.huayi.website.repository;

import com.huayi.website.entity.ProductAttachmentEntity;
import com.huayi.website.entity.ProductCategoryEntity;
import com.huayi.website.entity.ProductEntity;
import com.huayi.website.request.GoodsAddVo;
import com.huayi.website.utils.PagingResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.relational.core.sql.In;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository
public class ProductRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取所有分类
     * @return
     */
    public List<ProductCategoryEntity> getAllCategoryList() {
        String sql = "SELECT * FROM "+Tables.ProductCategory+" ORDER BY id desc";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(ProductCategoryEntity.class));
    }

    /**
     *
     * @param pageIndex
     * @param pageSize
     * @param title
     * @param categoryId
     * @return
     */
    public PagingResponse<ProductEntity> getAllList(int pageIndex, int pageSize,String title, Integer categoryId,Integer companyId){
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS p.*,pc.type FROM").append(Tables.Product).append(" as p ");
        sb.append(" LEFT JOIN ").append(Tables.ProductCategory).append(" as pc on pc.id = p.ref_id ");
        sb.append(" where 1=1 ");
        List<Object> params = new ArrayList<>();

        if (!StringUtils.isEmpty(title)) {
            sb.append(" AND p.product_name LIKE ? ");
            params.add("%" + title + "%");
        }
        if (categoryId!=null && categoryId > 0) {
            sb.append(" AND p.ref_id =? ");
            params.add(categoryId);
        }
        if (companyId!=null && companyId > 0) {
            sb.append(" AND p.company_id =? ");
            params.add(companyId);
        }
        sb.append(" ORDER BY p.sort desc,p.id DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(ProductEntity.class),params.toArray(new Object[params.size()]));

        int total = jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);

        return new PagingResponse<>(pageIndex, pageSize, total, list);
    }


    /**
     * 添加商品
     * @param addVo
     * @param userName
     * @return
     */
    @Transactional
    public Integer addGoods(GoodsAddVo addVo, String userName) {
        String goodsSQL = "INSERT INTO "+Tables.Product + "(product_name,index_img,sort,content,ref_id,company_id,product_number,product_ename,econtent) VALUE (?,?,?,?,?,?,?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(goodsSQL, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, addVo.getTitle());
                ps.setString(2, addVo.getImage());
                ps.setInt(3,addVo.getSort());
                ps.setString(4, addVo.getDetailText());
                ps.setInt(5, addVo.getCategoryId());
                ps.setInt(6, addVo.getCompanyId());
                ps.setString(7, addVo.getNumber());
                ps.setString(8, addVo.getEtitle());
                ps.setString(9, addVo.getDetaileText());

                return ps;
            }
        }, keyHolder);

        Integer goodsId = keyHolder.getKey().intValue();

        String attSQL =" INSERT INTO "+Tables.ProductAttachment+" (goods_id,attachment_type,url,create_on) VALUE (?,?,?,?)";

        //添加主图
        if(addVo.getImages()!=null && addVo.getImages().length>0) {
            for (var img : addVo.getImages()) {
                if(StringUtils.isEmpty(img) == false)
                    jdbcTemplate.update(attSQL, goodsId, 0, img, System.currentTimeMillis() / 1000);
            }
        }
        //添加视频
        if(StringUtils.isEmpty(addVo.getVideo())==false){
            jdbcTemplate.update(attSQL,goodsId,1,addVo.getVideo(),System.currentTimeMillis() / 1000);
        }
        //添加详情图片
        if(addVo.getDetailImages()!=null && addVo.getDetailImages().length>0) {
            for (var img : addVo.getDetailImages()) {
                if(StringUtils.isEmpty(img) == false)
                    jdbcTemplate.update(attSQL, goodsId, 3, img, System.currentTimeMillis() / 1000);
            }
        }

        return goodsId;
    }

    /**
     * 添加分类
     * @param title
     * @param detail
     * @param sort
     * @param image
     * @return
     */
    @Transactional
    public Integer addCategory(Integer categoryId, String title, String detail, Integer sort, String image){
        //如果存在，更新
        if(categoryId==0){
            String goodsSQL = "INSERT INTO " + Tables.ProductCategory + "(type,image,sort,content,create_time) VALUE (?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(goodsSQL, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, title);
                    ps.setString(2, image);
                    ps.setInt(3, sort);
                    ps.setString(4, detail);
                    ps.setObject(5, new Date());

                    return ps;
                }
            }, keyHolder);

            Integer goodsId = keyHolder.getKey().intValue();
            return goodsId;
        }else{
            //更新
            String goodsSQL = "UPDATE " + Tables.ProductCategory + "SET type=?,image=?,sort=?,content=? WHERE id=?";
            jdbcTemplate.update(goodsSQL,title,image,sort,detail,categoryId);
            return categoryId;
        }
    }

    public ProductEntity getDetailById(Integer id){
        try {
            var product = jdbcTemplate.queryForObject("select * from "+Tables.Product+" WHERE id=?",new BeanPropertyRowMapper<>(ProductEntity.class),id);
            //视频
            var video = jdbcTemplate.query("SELECT * FROM "+Tables.ProductAttachment+" WHERE goods_id=? AND attachment_type=1 LIMIT 1",new BeanPropertyRowMapper<>(ProductAttachmentEntity.class),id);
            if(video!=null && video.size()>0)
                product.setVideo(video.get(0).getUrl());
            //主图
            var imgs = jdbcTemplate.query("SELECT * FROM "+Tables.ProductAttachment+" WHERE goods_id=? AND attachment_type=0 ",new BeanPropertyRowMapper<>(ProductAttachmentEntity.class),id);
            if(imgs!=null && imgs.size()>0){
                product.setImages(imgs);
            }
            //详情图
            var detailImgs = jdbcTemplate.query("SELECT * FROM "+Tables.ProductAttachment+" WHERE goods_id=? AND attachment_type=3 ",new BeanPropertyRowMapper<>(ProductAttachmentEntity.class),id);
            if(detailImgs!=null && detailImgs.size()>0){
                product.setDetailImages(detailImgs);
            }
            return product;
        }catch (Exception e){
            return null;
        }
    }

    @Transactional
    public Integer editGoods(Integer productId,GoodsAddVo editVo, String userName) {
        try{
            var product = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.Product+" WHERE id=?",new BeanPropertyRowMapper<>(ProductEntity.class),productId);

            //更新主表信息
            jdbcTemplate.update("UPDATE "+Tables.Product+" SET product_name=?,index_img=?,content=?,ref_id=?,sort=?,company_id=?,product_number=?,product_ename=?,econtent=? WHERE id=?",
                    editVo.getTitle(),editVo.getImage(),editVo.getDetailText(),editVo.getCategoryId(),editVo.getSort(),editVo.getCompanyId(),editVo.getNumber(),editVo.getEtitle(),editVo.getDetaileText(),productId);

            //删除附件
            jdbcTemplate.update("DELETE FROM "+Tables.ProductAttachment+ " WHERE goods_id=?",productId);

            //添加附件
            String attSQL =" INSERT INTO "+Tables.ProductAttachment+" (goods_id,attachment_type,url,create_on) VALUE (?,?,?,?)";
            //添加主图
            if(editVo.getImages()!=null && editVo.getImages().length>0) {
                for (var img : editVo.getImages()) {
                    if(StringUtils.isEmpty(img) == false)
                        jdbcTemplate.update(attSQL, productId, 0, img, System.currentTimeMillis() / 1000);
                }
            }
            //添加视频
            if(StringUtils.isEmpty(editVo.getVideo())==false){
                jdbcTemplate.update(attSQL,productId,1,editVo.getVideo(),System.currentTimeMillis() / 1000);
            }
            //添加详情图片
            if(editVo.getDetailImages()!=null && editVo.getDetailImages().length>0) {
                for (var img : editVo.getDetailImages()) {
                    if(StringUtils.isEmpty(img) == false)
                        jdbcTemplate.update(attSQL, productId, 3, img, System.currentTimeMillis() / 1000);
                }
            }

            return  productId;

        }catch (Exception e){
            return 0;//失败
        }

    }

    @Transactional
    public void deleteGoods(Integer id) {
        try {
            jdbcTemplate.update("DELETE FROM " + Tables.ProductAttachment + " where goods_id=?", id);
            jdbcTemplate.update("DELETE FROM " + Tables.Product + " WHERE id=?", id);
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }
    public ProductCategoryEntity getCategoryById(Integer id) {
        try{
            return jdbcTemplate.queryForObject("SELECT * FROM "+Tables.ProductCategory+" WHERE id=?",new BeanPropertyRowMapper<>(ProductCategoryEntity.class),id);
        }catch (Exception e){
            return null;
        }
    }

    @Transactional
    public void deleteCategory(Integer id) {
        try {
            jdbcTemplate.update("DELETE FROM " + Tables.ProductCategory + " where id=?", id);

        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }
}
